﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[db_migration] 

AS
BEGIN
	
	DECLARE @cursor cursor
	DECLARE @tmp_pid int
	--КОПИРОВАНИЕ СПРАВОЧНИКОВ
	
	--заполняем справочник состояний продукции
	SET IDENTITY_INSERT NSI_PRODUCT_STATE ON;
	
	INSERT INTO NSI_PRODUCT_STATE (ID, NAME, LAST_UPDATE, LAST_UPDATE_USER)
		VALUES     (1, 'Заказан', NULL, NULL)
	INSERT INTO NSI_PRODUCT_STATE (ID, NAME, LAST_UPDATE, LAST_UPDATE_USER)
		VALUES     (2, 'В производстве', NULL, NULL)
	INSERT INTO NSI_PRODUCT_STATE (ID, NAME, LAST_UPDATE, LAST_UPDATE_USER)
		VALUES     (3, 'На складе', NULL, NULL)
	INSERT INTO NSI_PRODUCT_STATE (ID, NAME, LAST_UPDATE, LAST_UPDATE_USER)
		VALUES     (4, 'Отгружен', NULL, NULL)
	
	SET IDENTITY_INSERT NSI_PRODUCT_STATE OFF;
	
	--Копирование организации
	PRINT 'Копирование организации'
	SET IDENTITY_INSERT NSI_ORGANIZATION ON;
    INSERT INTO NSI_ORGANIZATION(SHORT_NAME, FULL_NAME, INN, KPP, OKPO, FULL_ADDR, ADDR_1, ADDR_2, ADDR_3, ADDR_4, COUNTRY, CITY, POST_INDEX, CONTACT, PHONE, FAX, LAST_UPDATE, LAST_UPDATE_USER, ID)
		SELECT FSO.dbo.NSI_ORGANIZATION.SHORT_NAME,FSO.dbo.NSI_ORGANIZATION.FULL_NAME,FSO.dbo.NSI_ORGANIZATION.INN, FSO.dbo.NSI_ORGANIZATION.KPP,FSO.dbo.NSI_ORGANIZATION.OKPO, FSO.dbo.NSI_ORGANIZATION.FULL_ADDR, FSO.dbo.NSI_ORGANIZATION.ADDR1, FSO.dbo.NSI_ORGANIZATION.ADDR2, FSO.dbo.NSI_ORGANIZATION.ADDR3, FSO.dbo.NSI_ORGANIZATION.ADDR4, FSO.dbo.NSI_ORGANIZATION.COUNTRY, FSO.dbo.NSI_ORGANIZATION.CITY, FSO.dbo.NSI_ORGANIZATION.POSTAL_CODE, FSO.dbo.NSI_ORGANIZATION.CONTACT,FSO.dbo.NSI_ORGANIZATION.PHONE,FSO.dbo.NSI_ORGANIZATION.FAX ,FSO.dbo.NSI_ORGANIZATION.LAST_UPDATE, FSO.dbo.NSI_ORGANIZATION.LAST_UPDATE_USER, FSO.dbo.NSI_ORGANIZATION.ID FROM FSO.dbo.NSI_ORGANIZATION;
	SET IDENTITY_INSERT NSI_ORGANIZATION OFF;
	
	--Копирование Единиц Измерения
	PRINT 'Копирование Единиц Измерения'
	SET IDENTITY_INSERT NSI_METER ON;
	INSERT INTO NSI_METER (ID, SHORT_NAME, FULL_NAME, LAST_UPDATE, LAST_UPDATE_USER) 
		SELECT FSO.dbo.NSI_UNIT.ID, FSO.dbo.NSI_UNIT.SHORT_NAME, FSO.dbo.NSI_UNIT.NAME, FSO.dbo.NSI_UNIT.LAST_UPDATE, FSO.dbo.NSI_UNIT.LAST_UPDATE_USER FROM FSO.dbo.NSI_UNIT
	SET IDENTITY_INSERT NSI_METER OFF;
	
	--Копирование валют
	SET IDENTITY_INSERT NSI_CURRENCY ON;
	INSERT INTO [dbo].[NSI_CURRENCY] ([ID], [CODE], [SHORT_NAME], [FULL_NAME], [LAST_UPDATE], [LAST_UPDATE_USER]) 
		SELECT FSO.dbo.NSI_CURRENCY.ID, FSO.dbo.NSI_CURRENCY.CODE, FSO.dbo.NSI_CURRENCY.SHORT_NAME, FSO.dbo.NSI_CURRENCY.FULL_NAME, FSO.dbo.NSI_CURRENCY.LAST_UPDATE, FSO.dbo.NSI_CURRENCY.LAST_UPDATE_USER FROM FSO.dbo.NSI_CURRENCY;
	SET IDENTITY_INSERT NSI_CURRENCY OFF;
	
	--Копирование курсов валют
	SET IDENTITY_INSERT NSI_EXCHANGE_RATE ON;
	INSERT INTO [dbo].[NSI_EXCHANGE_RATE] ([ID], [NSI_CURRENCY_ID], [DATE], [RATE], [LAST_UPDATE], [LAST_UPDATE_USER]) 
		SELECT FSO.dbo.NSI_EXCHANGE_RATE.ID, FSO.dbo.NSI_EXCHANGE_RATE.NSI_CURRENCY_ID, FSO.dbo.NSI_EXCHANGE_RATE.DATE, FSO.dbo.NSI_EXCHANGE_RATE.RATE, FSO.dbo.NSI_EXCHANGE_RATE.LAST_UPDATE, FSO.dbo.NSI_EXCHANGE_RATE.LAST_UPDATE_USER FROM FSO.dbo.NSI_EXCHANGE_RATE;
	SET IDENTITY_INSERT NSI_EXCHANGE_RATE OFF;
	
	--Указание к оплате
	SET IDENTITY_INSERT NSI_PAY_DIRECT ON;
	INSERT INTO NSI_PAY_DIRECT (ID, NAME, LAST_UPDATE, LAST_UPDATE_USER)
		SELECT FSO.dbo.NSI_INSTRUCTION_FOR_PAYMENT.ID, FSO.dbo.NSI_INSTRUCTION_FOR_PAYMENT.NAME, GETDATE() AS LAST_UPDATE, SUSER_NAME() AS LAST_UPDATE_USER FROM FSO.dbo.NSI_INSTRUCTION_FOR_PAYMENT
	SET IDENTITY_INSERT NSI_PAY_DIRECT OFF;
	
	--Материалы
	SET IDENTITY_INSERT NSI_MATERIAL ON;
	INSERT INTO NSI_MATERIAL (ID, NUMBER, NAME, NSI_METER_ID, LAST_UPDATE, LAST_UPDATE_USER)
		SELECT FSO.dbo.NSI_MATERIAL.ID, FSO.dbo.NSI_MATERIAL.MATERIAL_NUMBER, FSO.dbo.NSI_MATERIAL.NAME, FSO.dbo.NSI_MATERIAL.NSI_UNIT_ID, FSO.dbo.NSI_MATERIAL.LAST_UPDATE, FSO.dbo.NSI_MATERIAL.LAST_UPDATE_USER FROM FSO.dbo.NSI_MATERIAL
	SET IDENTITY_INSERT NSI_MATERIAL OFF;
	
	--Направление
	SET IDENTITY_INSERT NSI_TYPE_OF_ACTIVITY ON;
	INSERT INTO NSI_TYPE_OF_ACTIVITY (ID, CODE, NAME, LAST_UPDATE, LAST_UPDATE_USER)
		SELECT FSO.dbo.NSI_TYPE_OF_ACTIVITY.ID, FSO.dbo.NSI_TYPE_OF_ACTIVITY.CODE, FSO.dbo.NSI_TYPE_OF_ACTIVITY.NAME, FSO.dbo.NSI_TYPE_OF_ACTIVITY.LAST_UPDATE, FSO.dbo.NSI_TYPE_OF_ACTIVITY.LAST_UPDATE_USER FROM FSO.dbo.NSI_TYPE_OF_ACTIVITY
	SET IDENTITY_INSERT NSI_TYPE_OF_ACTIVITY OFF;
	
	--Услуги
	SET IDENTITY_INSERT NSI_SERVICE ON;
	INSERT INTO NSI_SERVICE (ID, CODE, SHORT_NAME, FULL_NAME, NSI_METER_ID, LAST_UPDATE, LAST_UPDATE_USER)
		SELECT FSO.dbo.NSI_SERVICE.ID, FSO.dbo.NSI_SERVICE.CODE, FSO.dbo.NSI_SERVICE.SHORT_NAME, FSO.dbo.NSI_SERVICE.FULL_NAME, FSO.dbo.NSI_SERVICE.NSI_UNIT_ID, FSO.dbo.NSI_SERVICE.LAST_UPDATE, FSO.dbo.NSI_SERVICE.LAST_UPDATE_USER FROM FSO.dbo.NSI_SERVICE
	SET IDENTITY_INSERT NSI_SERVICE OFF;
	
	--Товары (Только те у которых IS_WORK = 0)
	SET IDENTITY_INSERT NSI_PRODUCT ON;
	INSERT INTO NSI_PRODUCT (ID, CODE, SHORT_NAME, FULL_NAME, NSI_METER_ID, PROD_GROUP, N_DRAWING, LAST_UPDATE, LAST_UPDATE_USER)
		SELECT FSO.dbo.NSI_PRODUCT.ID, FSO.dbo.NSI_PRODUCT.CODE, FSO.dbo.NSI_PRODUCT.SHORT_NAME, FSO.dbo.NSI_PRODUCT.FULL_NAME, FSO.dbo.NSI_PRODUCT.NSI_UNIT_ID, FSO.dbo.NSI_PRODUCT.PRODUCT_GROUP, FSO.dbo.NSI_PRODUCT.DRAWING_NUMBER, FSO.dbo.NSI_PRODUCT.LAST_UPDATE, FSO.dbo.NSI_PRODUCT.LAST_UPDATE_USER FROM FSO.dbo.NSI_PRODUCT WHERE FSO.dbo.NSI_PRODUCT.IS_WORK = 0
	SET IDENTITY_INSERT NSI_PRODUCT OFF;
	
	--КОПИРОВАНИЕ СОВМЕСТНО ИСПОЛЬЗУЕМЫХ ОБЪЕКТОВ УЧЕТА
	
	--Договора
	
	--Нужно Удалить констрейт иерархии
	ALTER TABLE [CONTRACT] DROP CONSTRAINT [FK_CONTRACT_CONTRACT]
	
	DECLARE @C_ID int
	DECLARE @C_NUMBER nvarchar(50)
	DECLARE @C_START_DATE datetime
	DECLARE @C_NSI_ORGANIZATION_ID int
	DECLARE @C_NSI_TYPE_OF_ACTIVITY_ID int
	DECLARE @C_PARENT_CONTRACT_ID int
	DECLARE @C_DESCRIPTION nvarchar(MAX)
	DECLARE @C_LAST_UPDATE datetime
	DECLARE @C_LAST_UPDATE_USER nvarchar(50)
	
	SET @cursor = CURSOR SCROLL FOR
		SELECT c.ID, c.CONTRACT_NUMBER, c.CONTRACT_DATE, c.NSI_ORGANIZATION_ID, c.NSI_TYPE_OF_ACTIVITY_ID, c.PARENT_ID, c.[DESCRIPTION] ,c.LAST_UPDATE, c.LAST_UPDATE_USER FROM FSO.dbo.CONTRACT as c
	
	open @cursor
	--Цикл по всем договорам, заодно создаем 1 КП, 1 ЭТАП, ПСЦ на каждую валюту, 1 СЗ, 1 ЦН
	--Выбираем 1-ую строку
	FETCH NEXT FROM @cursor INTO @C_ID, @C_NUMBER, @C_START_DATE, @C_NSI_ORGANIZATION_ID, @C_NSI_TYPE_OF_ACTIVITY_ID, @C_PARENT_CONTRACT_ID, @C_DESCRIPTION, @C_LAST_UPDATE, @C_LAST_UPDATE_USER
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
			
		SET IDENTITY_INSERT [CONTRACT] ON;
		
		INSERT INTO [CONTRACT] (ID, NUMBER, [START_DATE], END_DATE, NSI_ORGANIZATION_ID, NSI_TYPE_OF_ACTIVITY_ID, TYPE_WORK, TYPE_DOC, PARENT_CONTRACT_ID, [DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES (@C_ID, @C_NUMBER, @C_START_DATE, @C_START_DATE, @C_NSI_ORGANIZATION_ID, @C_NSI_TYPE_OF_ACTIVITY_ID, NULL, NULL, @C_PARENT_CONTRACT_ID, @C_DESCRIPTION, @C_LAST_UPDATE, @C_LAST_UPDATE_USER, NULL)
		
		SET IDENTITY_INSERT [CONTRACT] OFF;
		
		SELECT @tmp_pid = PID FROM [CONTRACT] WHERE ID = @C_ID
		
		--Календарный план
		INSERT INTO CALENDAR_PLAN (NUMBER, START_DATE, END_DATE, CONTRACT_ID, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('КП_ДОГ_'+@C_NUMBER,@C_START_DATE,@C_START_DATE,@C_ID,NULL,NULL,@tmp_pid)
		--Этап календарного плана
		DECLARE @CP_ID int
		SELECT @CP_ID = ID FROM CALENDAR_PLAN WHERE PID = @tmp_pid
		
		INSERT INTO CP_STAGE (NUMBER, NAME, START_DATE, END_DATE, CALENDAR_PLAN_ID, PARENT_STAGE_ID, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('1','ЭТАП_ДОГ_'+@C_NUMBER,@C_START_DATE,@C_START_DATE,@CP_ID,NULL,NULL,NULL,@tmp_pid)
		
		--Протокол согласования цены
		--Необходимо пробежаться по всем валютам
		DECLARE @cur_cursor cursor
		DECLARE @cur_id int
		DECLARE @cur_name nvarchar(50)
		
		SET @cur_cursor = CURSOR SCROLL FOR	
			SELECT ID, SHORT_NAME FROM NSI_CURRENCY
		open @cur_cursor
		FETCH NEXT FROM @cur_cursor INTO @cur_id, @cur_name
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			INSERT INTO COST_PROTOCOL (NUMBER, CONTRACT_ID, START_DATE, END_DATE, NSI_CURRENCY_ID, IS_AGREED, LAST_UPDATE, LAST_UPDATE_USER, PID)
				VALUES ('ПРОТОКОЛ_'+@cur_name+'_ДОГ_'+@C_NUMBER, NULL,@C_START_DATE,@C_START_DATE, @cur_id, 0, NULL, NULL, @tmp_pid)
			
			FETCH NEXT FROM @cur_cursor INTO @cur_id, @cur_name
		END
		close @cur_cursor
		--Служебная записка
		INSERT INTO DIRECT (NUMBER, DATE, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('СЗ_ДОГ_'+@C_NUMBER, @C_START_DATE, NULL, NULL, NULL, @tmp_pid)
		
		--Цеховая накладная
		INSERT INTO GUILD_BILL (NUMBER, DATE, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('ЦН_ДОГ_'+@C_NUMBER, @C_START_DATE, NULL, NULL, NULL, @tmp_pid)
			
		--#############################  Исходящие счета    ############################################
		--Исходящие счета
		SET IDENTITY_INSERT INVOICE_OUT ON;
	
		INSERT INTO INVOICE_OUT (ID, NUMBER, [DATE], [SUM], NSI_CURRENCY_ID, ADVANCE_PERCENT, ADD_PAY_PERCENT, 
							[STATE], 
							[DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER, PID, [TYPE], NSI_ORGANIZATION_ID)
			SELECT ID, INVOICE_NUMBER, INVOICE_DATE, SUMM_CURRENCY, NSI_CURRENCY_ID, 
				CASE TYPE
				WHEN 'Аванс' THEN PREPAYMENT
				ELSE NULL 
				END, 
				CASE TYPE
				WHEN 'Доплата' THEN PREPAYMENT
				ELSE NULL 
				END, 
				CASE NSI_STATE_OF_INVOICE_ID
				WHEN 1 THEN 'Оплачен'
				WHEN 2 THEN 'Оплачен частично'
				WHEN 3 THEN 'Не оплачен'
				ELSE NULL
				END, 
				[DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER,
				@tmp_pid,
				1, 
				@C_NSI_ORGANIZATION_ID FROM FSO.dbo.INVOICE_OUT 
				WHERE CONTRACT_ID = @C_ID 
	
		SET IDENTITY_INSERT INVOICE_OUT OFF;
		--##############################################################################################
		
		--############################### Водящие платежи ##############################################
		SET IDENTITY_INSERT PAYMENT_IN ON;
		
		INSERT INTO PAYMENT_IN (ID, NUMBER, [TYPE], [DATE], SUM_RUB, NSI_ORGANIZATION_ID, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			SELECT DISTINCT p.ID, p.NUMBER, 1, p.[DATE], p.SUM_RUB, p.NSI_ORGANIZATION_ID, p.TYPE_PAYMENT, p.LAST_UPDATE, p.LAST_UPDATE_USER, @tmp_pid FROM FSO.dbo.PAYMENT_IN as p 
				INNER JOIN FSO.dbo.PAYMENT_IN_ON_INVOICE_OUT as pio ON pio.PAYMENTS_IN_ID = p.ID
				INNER JOIN FSO.dbo.INVOICE_OUT as i ON i.ID = pio.INVOICE_OUT_ID 
				WHERE i.CONTRACT_ID = @C_ID 
				AND
				p.ID NOT IN (SELECT ID FROM PAYMENT_IN)
				AND
				(p.TYPE_SERVICE IS NULL OR p.TYPE_SERVICE = 1)
		
		SET IDENTITY_INSERT PAYMENT_IN OFF;
		--##############################################################################################	
		
		--################################ Счет-фактуры ################################################
		SET IDENTITY_INSERT VAT_INVOICE ON;
		
		INSERT INTO VAT_INVOICE (ID, NUMBER, [DATE], SUM_RUB, [TYPE], IS_GUARANTY_LET, GUARANTY_LET_DATE, EXEC_TYPE, EXEC_NUMBER, LAST_UPDATE, LAST_UPDATE_USER, PID, NSI_ORGANIZATION_ID, [DESCRIPTION], INVOICE_OF_PAY_ADD_NUM)
			SELECT DISTINCT v.ID, v.NUMBER, v.[DATE], v.SUM_RUB, 
			CASE v.TYPE_PRODUCT
			WHEN 'Товары'
			THEN 'Товары/Работы'
			WHEN 'Работы'
			THEN 'Товары/Работы'
			WHEN 'Услуги'
			THEN 'Услуги'
			ELSE 'Товары/Работы'
			END,
			--Делаем IS_GUARANTY_LET = 1 В любом случае, если не 1, то в GUARANTY_LET_DATE запишем выпонены
			1,
			CASE v.IS_GUARANTY_LETTER
			WHEN 1
			THEN CONVERT(nvarchar(50),v.GUARANTY_LAETTER_DATE,103)
			ELSE 'ОБЯЗАТЕЛЬСТВА ВЫПОЛНЕНЫ'
			END,
			--EXEC_TYPE Если работы, то ТНН, работы Акт выполнения, иначе NULL
			CASE v.TYPE_PRODUCT
			WHEN 'Товары'
			THEN 'ТТН'
			WHEN 'Работы'
			THEN 'Акт выполнения'
			WHEN 'Услуги'
			THEN NULL
			ELSE 'ТТН'
			END,
			NULL, v.LAST_UPDATE, v.LAST_UPDATE_USER, @tmp_pid, v.NSI_ORGANIZTION_ID, v.COMMENTS, 
			--INVOICE_OF_PAY_ADD_NUM - может придется функцию написать, если не получится сделать по другому
			NULL 
			FROM FSO.dbo.VAT_INVOICE as v 
			INNER JOIN FSO.dbo.VAT_INVOICE_ON_INVOICE_OUT as vio ON vio.VAT_INVOICE_ID = v.ID
			INNER JOIN FSO.dbo.INVOICE_OUT as i ON vio.INVOICE_OUT_ID = i.ID
			WHERE i.CONTRACT_ID = @C_ID 
			AND 
			v.ID NOT IN (SELECT ID FROM VAT_INVOICE)
			AND (v.TYPE_PRODUCT is NULL OR v.TYPE_PRODUCT <> 'Услуги')
		
		SET IDENTITY_INSERT VAT_INVOICE OFF;
		--##############################################################################################
		
		--Выбираем следующую строку договора
		FETCH NEXT FROM @cursor INTO @C_ID, @C_NUMBER, @C_START_DATE, @C_NSI_ORGANIZATION_ID, @C_NSI_TYPE_OF_ACTIVITY_ID, @C_PARENT_CONTRACT_ID, @C_DESCRIPTION, @C_LAST_UPDATE, @C_LAST_UPDATE_USER
	END
	
	close @cursor
	
	--Обнуляем значение переменных
	SET @C_ID = null
	SET @C_NUMBER = null
	SET @C_START_DATE = null
	SET @C_NSI_ORGANIZATION_ID = null
	SET @C_NSI_TYPE_OF_ACTIVITY_ID = null
	SET @C_PARENT_CONTRACT_ID = null
	SET @C_DESCRIPTION = null
	SET @C_LAST_UPDATE = null
	SET @C_LAST_UPDATE_USER = null
	SET @cur_id = null
	SET @cur_name = null
	
	--Восстанавливаем констренйт иерархии
	ALTER TABLE [dbo].[CONTRACT]  WITH CHECK ADD  CONSTRAINT [FK_CONTRACT_CONTRACT] FOREIGN KEY([PARENT_CONTRACT_ID]) REFERENCES [dbo].[CONTRACT] ([ID])
	
	--################ Сборщик объектов не привязанных к договору и запись платежей и счет-фактур по услугам ####################
	INSERT INTO [CONTRACT] (NUMBER, [START_DATE], END_DATE, NSI_ORGANIZATION_ID, NSI_TYPE_OF_ACTIVITY_ID, TYPE_WORK, TYPE_DOC, PARENT_CONTRACT_ID, [DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('***СИСТЕМНЫЙ_КОНТРАКТ***', GETDATE(), GETDATE(), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
		
		SET @C_NUMBER = '***СИСТЕМНЫЙ_КОНТРАКТ***';
		
		--Находим ID контракта
				
		SELECT @tmp_pid = PID, @C_ID = ID, @C_START_DATE = [START_DATE]  FROM [CONTRACT] WHERE NUMBER = '***СИСТЕМНЫЙ_КОНТРАКТ***'
		
		--Календарный план
		INSERT INTO CALENDAR_PLAN (NUMBER, [START_DATE], END_DATE, CONTRACT_ID, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('КП_ДОГ_'+@C_NUMBER,@C_START_DATE,@C_START_DATE,@C_ID,NULL,NULL,@tmp_pid)
		--Этап календарного плана
		
		SELECT @CP_ID = ID FROM CALENDAR_PLAN WHERE PID = @tmp_pid
		
		INSERT INTO CP_STAGE (NUMBER, NAME, START_DATE, END_DATE, CALENDAR_PLAN_ID, PARENT_STAGE_ID, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('1','ЭТАП_ДОГ_'+@C_NUMBER,@C_START_DATE,@C_START_DATE,@CP_ID,NULL,NULL,NULL,@tmp_pid)
		
		--Протокол согласования цены
		--Необходимо пробежаться по всем валютам
		
		SET @cur_cursor = CURSOR SCROLL FOR	
			SELECT ID, SHORT_NAME FROM NSI_CURRENCY
		open @cur_cursor
		FETCH NEXT FROM @cur_cursor INTO @cur_id, @cur_name
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			INSERT INTO COST_PROTOCOL (NUMBER, CONTRACT_ID, START_DATE, END_DATE, NSI_CURRENCY_ID, IS_AGREED, LAST_UPDATE, LAST_UPDATE_USER, PID)
				VALUES ('ПРОТОКОЛ_'+@cur_name+'_ДОГ_'+@C_NUMBER, NULL,@C_START_DATE,@C_START_DATE, @cur_id, 0, NULL, NULL, @tmp_pid)
			
			FETCH NEXT FROM @cur_cursor INTO @cur_id, @cur_name
		END
		
		close @cur_cursor
		--Служебная записка
		INSERT INTO DIRECT (NUMBER, DATE, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('СЗ_ДОГ_'+@C_NUMBER, @C_START_DATE, NULL, NULL, NULL, @tmp_pid)
		
		--Цеховая накладная
		INSERT INTO GUILD_BILL (NUMBER, DATE, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			VALUES ('ЦН_ДОГ_'+@C_NUMBER, @C_START_DATE, NULL, NULL, NULL, @tmp_pid)
			
		--#############################  Исходящие счета    ############################################
		--Исходящие счета
		SET IDENTITY_INSERT INVOICE_OUT ON;
	
		INSERT INTO INVOICE_OUT (ID, NUMBER, [DATE], [SUM], NSI_CURRENCY_ID, ADVANCE_PERCENT, ADD_PAY_PERCENT, 
							[STATE], 
							[DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER, PID, [TYPE], NSI_ORGANIZATION_ID)
			SELECT ID, INVOICE_NUMBER, INVOICE_DATE, SUMM_CURRENCY, NSI_CURRENCY_ID, 
				CASE TYPE
				WHEN 'Аванс' THEN PREPAYMENT
				ELSE NULL 
				END, 
				CASE TYPE
				WHEN 'Доплата' THEN PREPAYMENT
				ELSE NULL 
				END, 
				CASE NSI_STATE_OF_INVOICE_ID
				WHEN 1 THEN 'Оплачен'
				WHEN 2 THEN 'Оплачен частично'
				WHEN 3 THEN 'Не оплачен'
				ELSE NULL
				END, 
				[DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER,
				@tmp_pid,
				1, 
				NULL FROM FSO.dbo.INVOICE_OUT 
				WHERE CONTRACT_ID IS NULL 
	
		SET IDENTITY_INSERT INVOICE_OUT OFF;
		--##############################################################################################
		
		--############################### Водящие платежи ##############################################
		-- здесь заносятся все платежи, кроме услуг
		SET IDENTITY_INSERT PAYMENT_IN ON;
		
		INSERT INTO PAYMENT_IN (ID, NUMBER, [TYPE], [DATE], SUM_RUB, NSI_ORGANIZATION_ID, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			SELECT DISTINCT p.ID, p.NUMBER, 1, p.[DATE], p.SUM_RUB, p.NSI_ORGANIZATION_ID, p.TYPE_PAYMENT, p.LAST_UPDATE, p.LAST_UPDATE_USER, @tmp_pid FROM FSO.dbo.PAYMENT_IN as p 
				LEFT JOIN FSO.dbo.PAYMENT_IN_ON_INVOICE_OUT as pio ON pio.PAYMENTS_IN_ID = p.ID
				LEFT JOIN FSO.dbo.INVOICE_OUT as i ON i.ID = pio.INVOICE_OUT_ID 
				WHERE (p.TYPE_SERVICE IS NULL OR p.TYPE_SERVICE = 1) AND (p.ID NOT IN (SELECT pt.ID FROM FSO2.dbo.PAYMENT_IN pt))
				--CONTRACT_ID IS NULL 
				--	AND
				--	  (p.TYPE_SERVICE IS NULL OR p.TYPE_SERVICE = 1) AND (p.ID NOT IN (SELECT pt.ID FROM FSO2.dbo.PAYMENT_IN pt))
		
		--Платежи, которые ни к чему не привязаны, но Тип "по счетам" или NULL
		
		--INSERT INTO PAYMENT_IN (ID, NUMBER, [TYPE], [DATE], SUM_RUB, NSI_ORGANIZATION_ID, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			--SELECT DISTINCT p.ID, p.NUMBER, 1, p.[DATE], p.SUM_RUB, p.NSI_ORGANIZATION_ID, p.TYPE_PAYMENT, p.LAST_UPDATE, p.LAST_UPDATE_USER, @tmp_pid FROM FSO.dbo.PAYMENT_IN as p 
				--WHERE p.ID NOT IN (SELECT pioi.PAYMENTS_IN_ID FROM FSO.dbo.PAYMENT_IN_ON_INVOICE_OUT as pioi) 
				--AND (p.TYPE_SERVICE IS NULL OR p.TYPE_SERVICE = 1)
		
		SET IDENTITY_INSERT PAYMENT_IN OFF;
		--##############################################################################################	
		
		--################################ Счет-фактуры ################################################
		--Здесь заносятся все счет-фактуры, кроме услуг
		SET IDENTITY_INSERT VAT_INVOICE ON;
		
		INSERT INTO VAT_INVOICE (ID, NUMBER, [DATE], SUM_RUB, [TYPE], IS_GUARANTY_LET, GUARANTY_LET_DATE, EXEC_TYPE, EXEC_NUMBER, LAST_UPDATE, LAST_UPDATE_USER, PID, NSI_ORGANIZATION_ID, [DESCRIPTION], INVOICE_OF_PAY_ADD_NUM)
			SELECT DISTINCT v.ID, v.NUMBER, v.[DATE], v.SUM_RUB, 
			'Товары/Работы',
			--Делаем IS_GUARANTY_LET = 1 В любом случае, если не 1, то в GUARANTY_LET_DATE запишем выпонены
			1,
			CASE v.IS_GUARANTY_LETTER
			WHEN 1
			THEN CONVERT(nvarchar(50),v.GUARANTY_LAETTER_DATE,103)
			ELSE 'ОБЯЗАТЕЛЬСТВА ВЫПОЛНЕНЫ'
			END,
			--EXEC_TYPE Если работы, то ТНН, работы Акт выполнения, иначе NULL
			CASE v.TYPE_PRODUCT
			WHEN 'Товары'
			THEN 'ТТН'
			WHEN 'Работы'
			THEN 'Акт выполнения'
			WHEN 'Услуги'
			THEN NULL
			ELSE 'ТТН'
			END,
			NULL, v.LAST_UPDATE, v.LAST_UPDATE_USER, @tmp_pid, v.NSI_ORGANIZTION_ID, v.COMMENTS, 
			--INVOICE_OF_PAY_ADD_NUM - может придется функцию написать, если не получится сделать по другому
			NULL 
			FROM FSO.dbo.VAT_INVOICE as v 
			LEFT JOIN FSO.dbo.VAT_INVOICE_ON_INVOICE_OUT as vio ON vio.VAT_INVOICE_ID = v.ID
			LEFT JOIN FSO.dbo.INVOICE_OUT as i ON vio.INVOICE_OUT_ID = i.ID
			WHERE i.CONTRACT_ID IS NULL
				AND 
				 (v.TYPE_PRODUCT is NULL OR v.TYPE_PRODUCT <> 'Услуги')
		
		SET IDENTITY_INSERT VAT_INVOICE OFF;
		
		--Платежи по услугам
		
		SET IDENTITY_INSERT PAYMENT_IN ON;
		
		INSERT INTO PAYMENT_IN (ID, NUMBER, [TYPE], [DATE], SUM_RUB, NSI_ORGANIZATION_ID, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
			SELECT p.ID, p.NUMBER, 0, p.[DATE], p.SUM_RUB, p.NSI_ORGANIZATION_ID, p.TYPE_PAYMENT, p.LAST_UPDATE, p.LAST_UPDATE_USER, @tmp_pid FROM FSO.dbo.PAYMENT_IN as p 
				WHERE p.TYPE_SERVICE = 0 AND p.ID NOT IN (SELECT piTmp.ID FROM PAYMENT_IN as piTmp)
		
		SET IDENTITY_INSERT PAYMENT_IN OFF;
		
		--Счет-фактуры по услугам
		SET IDENTITY_INSERT VAT_INVOICE ON;
		INSERT INTO VAT_INVOICE (ID, NUMBER, [DATE], SUM_RUB, [TYPE], IS_GUARANTY_LET, GUARANTY_LET_DATE, EXEC_TYPE, EXEC_NUMBER, LAST_UPDATE, LAST_UPDATE_USER, PID, NSI_ORGANIZATION_ID, [DESCRIPTION], INVOICE_OF_PAY_ADD_NUM)
			SELECT DISTINCT v.ID, v.NUMBER, v.[DATE], v.SUM_RUB, 
			'Услуги',
			--Делаем IS_GUARANTY_LET = 1 В любом случае, если не 1, то в GUARANTY_LET_DATE запишем выпонены
			1,
			CASE v.IS_GUARANTY_LETTER
			WHEN 1
			THEN CONVERT(nvarchar(50),v.GUARANTY_LAETTER_DATE,103)
			ELSE 'ОБЯЗАТЕЛЬСТВА ВЫПОЛНЕНЫ'
			END,
			--EXEC_TYPE 
			NULL,
			NULL, v.LAST_UPDATE, v.LAST_UPDATE_USER, NULL, v.NSI_ORGANIZTION_ID, v.COMMENTS, 
			--INVOICE_OF_PAY_ADD_NUM - может придется функцию написать, если не получится сделать по другому
			NULL 
			FROM FSO.dbo.VAT_INVOICE as v 
			WHERE v.TYPE_PRODUCT = 'Услуги'
		
		SET IDENTITY_INSERT VAT_INVOICE OFF;
		
	--###########################################################################################################################
	
	
	--ЧАСТЬ ПО ВХОДЯЩИМ СЧЕТАМ
	
	--Входящие счета
	SET IDENTITY_INSERT INVOICE_IN ON;
	
	INSERT INTO INVOICE_IN (ID, NUMBER, REG_NUMBER, AREAL, NSI_CONTRACT_ID, [DATE], [SUM], NSI_CURRENCY_ID, NSI_TYPE_OF_ACTIVITY_ID, N_FOLDER, PLAN_PAY_DATE, 
							NSI_PAY_DIRECT_ID, NSI_FINANCE_RESOURCE_ID, NSI_EXPENSE_ITEM_ID, CITY, NSI_ORGANIZATION_ID, CONTRACT_NUMBER, 
							CONTRACT_DATE, [STATE], [DESCRIPTION], LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT     ID, NUMBER, CODE, AREA, CONTRACT_ID, DATE, SUM_CUR, NSI_CURRENCY_ID, NSI_TYPE_OF_ACTIVITY_ID, FOLDER_NUM, PLAN_PAYMENT_DATE,
		NSI_INSTRACTION_FOR_PAYMENT_ID, NULL, NULL, CITY, NSI_ORGANIZATION_ID, CONCTRACT_NUMBER, CONTRACT_DATE, 
		CASE NSI_STATE_OF_INVOICE_ID
		WHEN 1 THEN 'Оплачен'
		WHEN 2 THEN 'Оплачен частично'
		WHEN 3 THEN 'Не оплачен'
		ELSE NULL
		END, 
		DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, NULL FROM FSO.dbo.INVOICE_IN
	
	SET IDENTITY_INSERT INVOICE_IN OFF;
	
	--Исходящие платежи
	
	SET IDENTITY_INSERT PAYMENT_OUT ON;
	
	INSERT INTO PAYMENT_OUT (ID, NUMBER, SUM_RUB, DATE, DESCRIPTION, LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT ID, PAYMENTS_NUMBER, SUMM, DATE, COMMENT, LAST_UPDATE, LAST_UPDATE_USER, NULL FROM FSO.dbo.PAYMENT_OUT

	SET IDENTITY_INSERT PAYMENT_OUT OFF;
	
	--Пункты Входящего счета
	SET IDENTITY_INSERT INVOICE_IN_ITEM ON;
	
	INSERT INTO INVOICE_IN_ITEM (ID, INVOICE_IN_ID, NSI_MATERIAL_ID, COMMENTS, QUANTITY, LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT ID, INVOICE_IN_ID, NSI_MATERIAL_ID, COMMENTS, QUANTITY, LAST_UPDATE, LAST_UPDATE_USER, NULL FROM FSO.dbo.INVOICE_IN_ITEM

	SET IDENTITY_INSERT INVOICE_IN_ITEM OFF;
	
	--Исходящий платеж по входящим счетам	
	SET IDENTITY_INSERT PAY_OUT_ON_INV_IN ON;
	
	INSERT INTO PAY_OUT_ON_INV_IN (ID, PAYMENT_OUT_ID, INVOICE_IN_ID, SUM_CUR, SUM_RUB, COMMENTS, LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT ID, PAYMENT_OUT_ID, INVOICE_IN_ID, SUM_CUR, [SUM], COMMENTS, LAST_UPDATE, LAST_UPDATE_USER, NULL FROM FSO.dbo.PAYMENT_OUT_ON_INVOICE_IN
	
	SET IDENTITY_INSERT PAY_OUT_ON_INV_IN OFF;
	
	--ЧАСТЬ ПО ИСХОДЯЩИМ СЧЕТАМ - САМАЯ ИНТЕРЕСНАЯ :)
	
	-- к этому момент, по идее :), должны быть загружены и все счета, платежи и счет-фактуры, поэтому будем делать join таблиц из разных схем
	
	--Входящие платежи по исходящим счетам 
	SET IDENTITY_INSERT PAY_IN_ON_INV_OUT ON;
	
	INSERT INTO PAY_IN_ON_INV_OUT (ID, PAYMENT_IN_ID, INVOICE_OUT_ID, SUM_CUR, SUM_RUB, LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT  piio.ID, piio.PAYMENTS_IN_ID, piio.INVOICE_OUT_ID, piio.SUM_CUR, 
				piio.[SUM], piio.LAST_UPDATE, piio.LAST_UPDATE_USER, p.PID 
				FROM FSO.dbo.PAYMENT_IN_ON_INVOICE_OUT as piio 
				INNER JOIN
				PAYMENT_IN as p ON p.ID = piio.PAYMENTS_IN_ID
				--WHERE p.[TYPE] = 1
	
	SET IDENTITY_INSERT PAY_IN_ON_INV_OUT OFF;
	
	--Входящий платеж по услугам
	SET IDENTITY_INSERT PAY_IN_ON_SERVICE ON;
	
	INSERT INTO PAY_IN_ON_SERVICE (ID, PAYMENT_IN_ID, NSI_SERVICE_ID, QAUNTITY, LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT pis.ID, pis.PAYMENT_IN_ID, pis.NSI_SERVICE_ID, 1, pis.LAST_UPDATE,
			pis.LAST_UPDATE_USER,NULL FROM FSO.dbo.PAYMENT_IN_ON_SERVICE as pis
			
	SET IDENTITY_INSERT PAY_IN_ON_SERVICE OFF;
	
	--########################################## Пункты исходящего счета ########################################
	
	SET @cursor = CURSOR SCROLL FOR
		SELECT ioi.ID, ioi.INVOICE_OUT_ID, ioi.NSI_PRODUCT_ID, ioi.QUANTITY, cps.ID, i.PID, pr.SHORT_NAME, pr.IS_WORK, i.NSI_CURRENCY_ID
		 FROM FSO.dbo.INVOICE_OUT_ITEM as ioi 
			INNER JOIN INVOICE_OUT as i ON i.ID = ioi.INVOICE_OUT_ID
			INNER JOIN CP_STAGE as cps ON cps.PID = i.PID
			INNER JOIN FSO.dbo.NSI_PRODUCT as pr ON pr.ID = ioi.NSI_PRODUCT_ID
		
	DECLARE @IOI_ID int
	DECLARE @IOI_IO_ID int
	DECLARE @IOI_PR_ID int
	DECLARE @IOI_COUNT int
	DECLARE @CP_STAGE int
	DECLARE @PROD_T bit
	DECLARE @PR_NAME nvarchar(50)
	--Переменная итерации
	DECLARE @ITER int
	
	open @cursor
	FETCH NEXT FROM @cursor INTO @IOI_ID, @IOI_IO_ID, @IOI_PR_ID, @IOI_COUNT, @CP_STAGE, @tmp_pid, @PR_NAME, @PROD_T, @cur_id
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @ITER = 0
		
		IF @IOI_COUNT IS NULL OR @IOI_COUNT <1
		BEGIN
			SET @IOI_COUNT = 1
			--Вкомментарий Счета Напишем, что было указано NULL или 0 Товаров данного пункта
			DECLARE @INV_DESCRIPTION nvarchar(max)
			--DECLARE @TMP_VAT_PR_NAME nvarchar(50)
			
			SELECT @INV_DESCRIPTION = ISNULL([DESCRIPTION],'') FROM INVOICE_OUT WHERE ID = @IOI_IO_ID
			
			SET @INV_DESCRIPTION = @INV_DESCRIPTION + CHAR(13) + CHAR(10) +
								'У Товара "' + @PR_NAME + '" не было указано количество! Проверьте данные!'
								
			UPDATE INVOICE_OUT SET [DESCRIPTION] = @INV_DESCRIPTION WHERE ID = @IOI_IO_ID
			
		END
		
		
		
		WHILE @ITER < @IOI_COUNT
		BEGIN
		--ВСТАВКА в таблицу PRODUCT_UNIT
			DECLARE @PR_UNIT_ID int
			DECLARE @COST_PROT_ID int
			
			INSERT INTO PRODUCT_UNIT
                      (CP_STAGE_ID, [TYPE], NSI_PRODUCT_ID, NAME, DIRECT_NUMBER, GUILD_BILL_NUMBER, VAT_INVOICE_ID, 
                      LAST_UPDATE, LAST_UPDATE_USER, PID, NSI_PRODUCT_STATE_ID)
			VALUES (@CP_STAGE,
				--TYPE если IS_WORK = 1 => TYPE = 0, IS_WORK=0 или NULL => TYPE = 1
				CASE @PROD_T
				WHEN 1 THEN 0
				ELSE 1
				END,
				--Определяеем значение NSI_PRODUCT_ID, IS_WORK = 1 => NSI_PRODUCT_ID = -1, IS_WORK=0 или NULL => TYPE = @IOI_PR_ID
				CASE @PROD_T
				WHEN 1 THEN (-1)
				ELSE @IOI_PR_ID
				END,
				@PR_NAME, NULL, NULL, NULL, NULL, NULL, @tmp_pid, NULL)
			
			SET @PR_UNIT_ID = SCOPE_IDENTITY()
				
			IF @cur_id IS NULL
			BEGIN
				SET @cur_id = FSO2.dbo.f_get_curID('RUB')
			END	
			
			SELECT @COST_PROT_ID = ID FROM COST_PROTOCOL WHERE PID = @tmp_pid AND NSI_CURRENCY_ID = @cur_id
			
			--Указание цены на только-что вставленный продукт цену везде устанавливаем в 1
			INSERT INTO PRODUCT_COST (COST_PROTOCOL_ID, PRODUCT_UNIT_ID, COST, LAST_UPDATE, LAST_UPDATE_USER, PID)
				VALUES     (@COST_PROT_ID, @PR_UNIT_ID, 1, NULL, NULL, @tmp_pid)
				
			--Связываем продукт со счетом, сумму устанавливаем тоже 1
			INSERT INTO INVOICE_OUT_ITEM (INVOICE_OUT_ID, PRODUCT_UNIT_ID, [SUM], LAST_UPDATE, LAST_UPDATE_USER, PID)
				VALUES     (@IOI_IO_ID, @PR_UNIT_ID, 1, NULL, NULL, @tmp_pid)
			
			SET @ITER = @ITER + 1
		END
		
		FETCH NEXT FROM @cursor INTO @IOI_ID, @IOI_IO_ID, @IOI_PR_ID, @IOI_COUNT, @CP_STAGE, @tmp_pid, @PR_NAME, @PROD_T, @cur_id
	END
	
	close @cursor
	-- Обнуляем значения переменных
	SET @IOI_ID = null
	SET @IOI_IO_ID = null
	SET @IOI_PR_ID = null
	SET @IOI_COUNT = null
	SET @CP_STAGE = null
	SET @PROD_T = null
	SET @PR_NAME = null
	--##################################################################################################################################
	--########################################## Пункты Счет-фактуры ########################################
	
	--Счет фактура по услугам
	
	INSERT INTO VAT_INV_ON_SERVICE (NSI_SERVICE_ID, QUANTITY, VAT_INVOICE_ID, LAST_UPDATE, LAST_UPDATE_USER, PID)
		SELECT vi.NSI_SERVICE_ID, vi.QUANTITY, vi.VAT_INVOICE_ID, NULL, NULL, NULL
		FROM FSO.dbo.VAT_INVOICE_ITEM as vi
		INNER JOIN FSO.dbo.VAT_INVOICE as v ON (v.ID = vi.VAT_INVOICE_ID AND v.TYPE_PRODUCT = 'Услуги')
		
	--Пункты счет фактуры
	--Считаем, что Все цены на товары по счет-фактуре указаны в рублях
	SET @cur_id = dbo.f_get_curID('RUB');
	
	SET @cursor = CURSOR SCROLL FOR
		SELECT vi.VAT_INVOICE_ID, vi.NSI_PRODUCT_ID, vi.QUANTITY, cps.ID, cp.ID, d.ID, gb.ID, v.PID, pr.SHORT_NAME, pr.IS_WORK
		FROM FSO.dbo.VAT_INVOICE_ITEM as vi
			INNER JOIN VAT_INVOICE as v ON v.ID = vi.VAT_INVOICE_ID
			INNER JOIN CP_STAGE as cps ON cps.PID = v.PID
			INNER JOIN COST_PROTOCOL as cp ON (cp.PID = v.PID AND cp.NSI_CURRENCY_ID = @cur_id)
			INNER JOIN GUILD_BILL as gb ON gb.PID = v.PID
			INNER JOIN DIRECT as d ON d.PID = v.PID
			INNER JOIN FSO.dbo.NSI_PRODUCT as pr ON pr.ID = vi.NSI_PRODUCT_ID
			WHERE v.TYPE = 'Товары/Работы'
	
	DECLARE @VI_VAT_INV_ID int
	DECLARE @VI_PR_UNIT_ID int
	DECLARE @VI_COUNT int
	DECLARE @DIRECT_ID int
	DECLARE @GUILD_BILL_ID int
	DECLARE @COST_PROTOCOL_ID int
	
	open @cursor
	FETCH NEXT FROM @cursor INTO @VI_VAT_INV_ID, @VI_PR_UNIT_ID, @VI_COUNT, @CP_STAGE, @COST_PROTOCOL_ID, @DIRECT_ID, @GUILD_BILL_ID, @tmp_pid, @PR_NAME, @PROD_T
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @ITER = 0
		
		IF @VI_COUNT IS NULL OR @VI_COUNT <1
		BEGIN
			SET @VI_COUNT = 1
			--Вкомментарий СФ Напишем, что было указано NULL или 0 Товаров данного пункта
			DECLARE @VAT_DESCRIPTION nvarchar(max)
			--DECLARE @TMP_VAT_PR_NAME nvarchar(50)
			
			SELECT @VAT_DESCRIPTION = ISNULL([DESCRIPTION],'') FROM VAT_INVOICE WHERE ID = @VI_VAT_INV_ID
			
			SET @VAT_DESCRIPTION = @VAT_DESCRIPTION + CHAR(13) + CHAR(10) +
								'У Товара "' + @PR_NAME + '" не было указано количество! Проверьте данные!'
								
			UPDATE VAT_INVOICE SET [DESCRIPTION] = @VAT_DESCRIPTION WHERE ID = @VI_VAT_INV_ID
			
		END
		
		WHILE @ITER < @VI_COUNT
		BEGIN
				
			--Одновременно указываем СЗ и ЦН и СФ 		
			INSERT INTO PRODUCT_UNIT
                      (CP_STAGE_ID, [TYPE], NSI_PRODUCT_ID, NAME, DIRECT_NUMBER, GUILD_BILL_NUMBER, VAT_INVOICE_ID, 
                      LAST_UPDATE, LAST_UPDATE_USER, PID, NSI_PRODUCT_STATE_ID)
			VALUES (@CP_STAGE,
				--TYPE если IS_WORK = 1 => TYPE = 0, IS_WORK=0 или NULL => TYPE = 1
				CASE @PROD_T
				WHEN 1 THEN 0
				ELSE 1
				END,
				--Определяеем значение NSI_PRODUCT_ID, IS_WORK = 1 => NSI_PRODUCT_ID = -1, IS_WORK=0 или NULL => TYPE = @IOI_PR_ID
				CASE @PROD_T
				WHEN 1 THEN (-1)
				ELSE @VI_PR_UNIT_ID
				END,
				@PR_NAME,
				CASE @PROD_T
				WHEN 1 THEN NULL
				ELSE @DIRECT_ID
				END, 
				CASE @PROD_T
				WHEN 1 THEN NULL
				ELSE @DIRECT_ID
				END, 
				@VI_VAT_INV_ID, NULL, NULL, @tmp_pid, NULL)
			
			
			SET @PR_UNIT_ID = SCOPE_IDENTITY();
			
			--Указание цены на только-что вставленный продукт цену везде устанавливаем в 1
			INSERT INTO PRODUCT_COST (COST_PROTOCOL_ID, PRODUCT_UNIT_ID, COST, LAST_UPDATE, LAST_UPDATE_USER, PID)
				VALUES     (@COST_PROTOCOL_ID, @PR_UNIT_ID, 1, NULL, NULL, @tmp_pid)
			
			SET @ITER = @ITER + 1
		END
		
		SET @VI_VAT_INV_ID = null
		SET @VI_PR_UNIT_ID = null
		SET @VI_COUNT = null
		SET @DIRECT_ID = null
		SET @GUILD_BILL_ID = null
		SET @COST_PROTOCOL_ID = null
		SET @CP_STAGE = null
		SET @PROD_T = null
		SET @PR_NAME = null
		
		FETCH NEXT FROM @cursor INTO @VI_VAT_INV_ID, @VI_PR_UNIT_ID, @VI_COUNT, @CP_STAGE, @COST_PROTOCOL_ID, @DIRECT_ID, @GUILD_BILL_ID, @tmp_pid, @PR_NAME, @PROD_T
	END
	
	close @cursor
	--##########################################################################################################
	--Вставка в счет комментария в по связи со счет-фактурой
	
	DECLARE @IO_ID int
	
	DECLARE @InStr nvarchar(1024)
	DECLARE @tmp_cursor cursor
	DECLARE @VI_NUM nvarchar(50)
	DECLARE @VI_DATE nvarchar(50)
	DECLARE @VI_SUM nvarchar(50)
	DECLARE @VIO_SUM nvarchar(50)
	
	SET @cursor = CURSOR SCROLL FOR
		SELECT ID FROM INVOICE_OUT
	
	open @cursor
	
	FETCH NEXT FROM @cursor INTO @IO_ID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @tmp_cursor = CURSOR SCROLL FOR
			SELECT v.NUMBER, CONVERT(nvarchar(50),v.DATE,103), STR(v.SUM_RUB, 18,2), STR(vio.[SUM],18,2) FROM FSO.dbo.VAT_INVOICE_ON_INVOICE_OUT as vio 
				INNER JOIN VAT_INVOICE as v ON v.ID = vio.VAT_INVOICE_ID
				WHERE vio.INVOICE_OUT_ID = @IO_ID
		
		SELECT @InStr = ISNULL([DESCRIPTION] + CHAR(13) + CHAR(10), ' ') FROM INVOICE_OUT	WHERE ID = @IO_ID
		
		open @tmp_cursor
		
		FETCH NEXT FROM @tmp_cursor INTO @VI_NUM, @VI_DATE, @VI_SUM, @VIO_SUM
		
		IF @@FETCH_STATUS = 0
		BEGIN
			SET @InStr = @InStr + 'Счет Фактуры' + CHAR(13) + CHAR(10)
		
			SET @InStr = @InStr + '№' + SPACE(17) + 'Дата' + SPACE(14) + 'Сумма' + SPACE(13) + 'Оплаченная сумма'+ CHAR(13) + CHAR(10)
		END
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @InStr = @InStr + ISNULL(@VI_NUM,'') + SPACE(18-LEN(ISNULL(@VI_NUM,''))) + 
						ISNULL(@VI_DATE,'') + SPACE(14-LEN(ISNULL(@VI_DATE,''))) + 
						LTRIM(ISNULL(@VI_SUM,'')) + SPACE(18-LEN(LTRIM(ISNULL(@VI_SUM,'')))) + 
						LTRIM(ISNULL(@VIO_SUM,'')) + CHAR(13) + CHAR(10)
		
			FETCH NEXT FROM @tmp_cursor INTO @VI_NUM, @VI_DATE, @VI_SUM, @VIO_SUM		
		END
		
		close @tmp_cursor
		
		--PRINT @InStr
		
		UPDATE INVOICE_OUT SET [DESCRIPTION] = @InStr WHERE ID = @IO_ID
		
		FETCH NEXT FROM @cursor INTO @IO_ID
	END
	
	close @cursor
	
	--Вставка комментария к счет-фактуре о связи со счетом
	DECLARE @VI_ID int
	
	--DECLARE @InStr nvarchar(1024)
	--DECLARE @tmp_cursor cursor
	DECLARE @IO_NUM nvarchar(50)
	DECLARE @IO_DATE nvarchar(50)
	--DECLARE @VI_SUM nvarchar(50)
	--DECLARE @VIO_SUM nvarchar(50)
	
	SET @cursor = CURSOR SCROLL FOR
		SELECT ID FROM VAT_INVOICE
	
	open @cursor
	
	FETCH NEXT FROM @cursor INTO @VI_ID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @tmp_cursor = CURSOR SCROLL FOR
			SELECT i.NUMBER, CONVERT(nvarchar(50),i.DATE,103), STR(vio.[SUM],18,2) FROM FSO.dbo.VAT_INVOICE_ON_INVOICE_OUT as vio 
				INNER JOIN INVOICE_OUT as i ON i.ID = vio.INVOICE_OUT_ID
				WHERE vio.VAT_INVOICE_ID = @VI_ID
		
		SELECT @InStr = ISNULL([DESCRIPTION] + CHAR(13) + CHAR(10), ' ') FROM VAT_INVOICE WHERE ID = @VI_ID
		
		open @tmp_cursor
		
		FETCH NEXT FROM @tmp_cursor INTO @IO_NUM, @IO_DATE, @VIO_SUM
		
		IF @@FETCH_STATUS = 0
		BEGIN
			SET @InStr = @InStr + 'Счета' + CHAR(13) + CHAR(10)
		
			SET @InStr = @InStr + '№' + SPACE(17) + 'Дата' + SPACE(14) + 'Оплаченная сумма'+ CHAR(13) + CHAR(10)
		END
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @InStr = @InStr + ISNULL(@IO_NUM,'') + SPACE(18-LEN(ISNULL(@IO_NUM,''))) + 
						ISNULL(@IO_DATE,'') + SPACE(14-LEN(ISNULL(@IO_DATE,''))) + 
						LTRIM(ISNULL(@VIO_SUM,'')) + CHAR(13) + CHAR(10)
		
			FETCH NEXT FROM @tmp_cursor INTO @IO_NUM, @IO_DATE, @VIO_SUM		
		END
		
		close @tmp_cursor
		
		--PRINT @InStr
		
		UPDATE VAT_INVOICE SET [DESCRIPTION] = @InStr WHERE ID = @VI_ID
		
		FETCH NEXT FROM @cursor INTO @VI_ID
	END
	
	close @cursor
    
END